package com.mairuide.bill.titlepool;

import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.UnsupportedEncodingException;
import java.math.BigDecimal;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.security.core.context.SecurityContextHolder;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import com.mairuide._frame.base.BaseRepository;
import com.mairuide._frame.base.BaseServiceProxy;
import com.mairuide._frame.config.LoginUser;
import com.mairuide._frame.utils.SecurityUtil;
import com.mairuide.bill.DealFlow;
import com.mairuide.bill.DealFlowService;
import com.mairuide.bill.utils.DateUtils;
import com.mairuide.bill.utils.ExcelUtils;
import com.mairuide.platmanager.PlatManager;

/**
 * @author: zyh
 * @description:
 * @date: 2018/11/13 0013
 * @company: MRD
 */

@Service
public class DealFlowTitlePoolService extends BaseServiceProxy<DealFlowTitlePool> {

    @Autowired
    private DealFlowTitlePoolRepository dealFlowTitlePoolRepository;
    @Autowired
    private DealFlowTitleRepository dealFlowTitleRepository;
    @Autowired
    private DealFlowService dealFlowService;

    @Override
    protected BaseRepository<DealFlowTitlePool> getBaseRepository() {
        return dealFlowTitlePoolRepository;
    }

    /**
     * 查询标题是否存在
     * @return
     */
    public boolean queryBankTitleName(String title) {
        DealFlowTitlePool dealFlowTitlePool = dealFlowTitlePoolRepository.findByBankTitleName(title);
        if (null!=dealFlowTitlePool){
            return true;
        }
        return false;
    }

    //根据标题名模糊查询
    public List<DealFlowTitlePool> findByBankTitleNameLike(String bankTitle){
        List<DealFlowTitlePool> byBankTitleNameLike = dealFlowTitlePoolRepository.findByBankTitleNameLike(bankTitle);
        return byBankTitleNameLike;
    };

    /**
     * 根据标题key获取标题池列表
     * @return
     */
    public List<DealFlowTitlePool> getTitlePoolList(Integer id){
        LoginUser loginUser = (LoginUser) SecurityContextHolder.getContext().getAuthentication().getPrincipal();
        PlatManager platManager = loginUser.getPlatManager();
        Integer managerId = platManager.getId();
        List<DealFlowTitlePool> dealFlowTitlePools = null;
        if (managerId==1){//管理员可以看到所有信息
            dealFlowTitlePools = dealFlowTitlePoolRepository.findByTitleIdAndIsDelete(id,0);
        }else {//用户只能看到自己添加标题
            dealFlowTitlePools = dealFlowTitlePoolRepository.findByTitleIdAndCreateIdAndIsDelete(id,managerId,0);
        }
//        for (DealFlowTitlePool dd: dealFlowTitlePools){
//            String bankTitle;
//			try {
//				bankTitle = SecurityUtil.decrypt(dd.getBankTitleName());
//				dd.setBankTitleName(bankTitle);
//			} catch (UnsupportedEncodingException e) {
//				e.printStackTrace();
//			}
//        }
        return dealFlowTitlePools;
    }

    /**
     * 读入TXT文件 只能读取使用tab分割的TXT文件
     */
    public void leadFileTxt(MultipartFile file) {
        List<Map<String,String>> list = new ArrayList<>();
        //用来存放账单标题与标题池的对应关系
        Map<String , String> titleMap = new HashMap<>();
        try {
            InputStream is = file.getInputStream();
            InputStreamReader isr = new InputStreamReader(is,"gbk");
            BufferedReader br = new BufferedReader(isr);
            String lineTxt = null;
            //获取表头数组
            lineTxt = br.readLine();
            String[] split = lineTxt.split("\t");
            //标题行与标题池做对应，找到标题对应字段
            for (int j=0;j<split.length;j++){
                String replace = split[j].replace(" ", "");
                DealFlowTitlePool dealPool = dealFlowTitlePoolRepository.findByBankTitleName(replace);
                if (null!=dealPool){
                    DealFlowTitle one = dealFlowTitleRepository.findOne(dealPool.getTitleId());
                    titleMap.put(split[j] ,one.getMappingField());
                }
            }
            //循环内容体
            while ((lineTxt = br.readLine()) != null) {
                DealFlow deal = new DealFlow();
                String[] sp = lineTxt.split("\t");
                for (int i=0;i<sp.length;i++){
                    //把对应标题的数据放入实体中
                    switch (titleMap.get(split[i])){
                        case "交易主体": deal.setAccountBody(sp[i]);break;
                        case "交易主体账号": deal.setAccountBodyNumber(sp[i]);break;
                        case "交易主体卡号": deal.setAccountBodyCard(sp[i]);break;
                        case "收付标志": deal.setSign(Integer.valueOf(sp[i]));break;
//                        case "交易日期": deal.setTradeDate();break;
                        case "交易对手": deal.setAccountRival(sp[i]);break;
                        case "交易对手账号": deal.setAccountRivalNumber(sp[i]);break;
                        case "交易对手卡号": deal.setAccountRivalCard(sp[i]);break;
                        case "交易金额": deal.setAccountMoney(new BigDecimal(sp[i]));break;
                        case "交易后余额": deal.setSurplusMoney(new BigDecimal( sp[i]));break;
                        case "摘要": deal.setContentAbstract( sp[i]);break;
                        case "交易主体归属行": deal.setAccountBodyBank( sp[i]);break;
                        case "交易对手归属行": deal.setAccountRivalBank( sp[i]);break;
                        case "交易地点": deal.setAccountAddress( sp[i]);break;
                        case "交易方式": deal.setAccountMode( sp[i]);break;
                        case "交易流水号": deal.setSerialNumber(sp[i]);break;
                        case "MAC地址": deal.setMacAddress( sp[i]);break;
                        case "IP地址": deal.setIpAddress( sp[i]);break;
                        case "币种": deal.setCurrency( sp[i]);break;
                        case "备注": deal.setRemarks( sp[i]);break;
                        default: break;
                    }
                }

            }
            br.close();
        } catch (Exception e) {
            System.out.println("文件读取错误!");
        }

    }

    /**
     * 读入CSV文件
     */
    public Map<String, Object> leadFileCSV(MultipartFile file,String uid,Integer id,String uuid){
        List<DealFlow> list = new ArrayList<>();
        Map<String, Object> result = new HashMap<>();
        //用来存放账单标题与标题池的对应关系
        Map<String , String> titleMap = new HashMap<>();
        String[] colunms =new String[50];
        try {
            InputStream is = file.getInputStream();
            InputStreamReader isr = new InputStreamReader(is,"gbk");
            BufferedReader br = new BufferedReader(isr);
            String lineTxt = null;
            //获取表头数组
            lineTxt = br.readLine();
            String[] split = lineTxt.split(",");
            //标题行与标题池做对应，找到标题对应字段
            for (int j=0;j<split.length;j++){
                String repltttace = split[j].replace("\"", "").trim();
                String replace = repltttace.replace(" ", "");
//                replace = SecurityUtil.encrypt(replace);
                DealFlowTitlePool dealPool = dealFlowTitlePoolRepository.findByBankTitleName(replace);
                if (null!=dealPool){
                    DealFlowTitle one = dealFlowTitleRepository.findOne(dealPool.getTitleId());
                    titleMap.put(split[j] ,one.getMappingField());
                    colunms[j] = split[j];
                }
            }
            //循环内容体
            while ((lineTxt = br.readLine()) != null) {
                DealFlow deal = new DealFlow();
                String[] sp = lineTxt.split(",");
                //交易日期两个字段就拼接，一个字段则不拼接
                String date = "";
                for (int i=0;i<sp.length;i++){
                    if (colunms[i]!=null){
                        sp[i] = sp[i].replace("\"", "").trim();
                        //把对应标题的数据放入实体中
                        switch (titleMap.get(colunms[i])){
                            case "交易主体": deal.setAccountBody(sp[i]);break;
                            case "交易主体账号": deal.setAccountBodyNumber(sp[i]);break;
                            case "交易主体卡号": if (deal.getAccountBodyCard()==null)deal.setAccountBodyCard(sp[i]);break;
                            case "收付标志":
                                if ("0".equals(sp[i])||"C".equals(sp[i])||"进".equals(sp[i])||"贷".equals(sp[i])){
                                    deal.setSign(0);
                                } else if ("1".equals(sp[i])||"D".equals(sp[i])||"出".equals(sp[i])||"借".equals(sp[i])){
                                    deal.setSign(1);
                                }break;
                            case "日期时间":
                                date += " "+sp[i] ;
                                date = date.trim() ;break ;
                                //deal.setTradeDate(date.trim());break;
                            case "交易对手": deal.setAccountRival(sp[i]);break;
                            case "交易对手账号": deal.setAccountRivalNumber(sp[i]);break;
                            case "交易对手卡号": deal.setAccountRivalCard(sp[i]);break;
                            case "交易金额": if (!"".equals(sp[i])&&sp[i]!=null)deal.setAccountMoney(new BigDecimal(sp[i]));break;
                            case "交易后余额": if (!"".equals(sp[i])&&sp[i]!=null)deal.setSurplusMoney(new BigDecimal( sp[i]));break;
                            case "摘要": deal.setContentAbstract( sp[i]);break;
                            case "交易主体归属行": deal.setAccountBodyBank( sp[i]);break;
                            case "交易对手归属行": deal.setAccountRivalBank( sp[i]);break;
                            case "交易地点": deal.setAccountAddress( sp[i]);break;
                            case "交易方式": deal.setAccountMode( sp[i]);break;
                            case "交易流水号": deal.setSerialNumber(sp[i]);break;
                            case "MAC地址": deal.setMacAddress( sp[i]);break;
                            case "IP地址": deal.setIpAddress( sp[i]);break;
                            case "币种": deal.setCurrency( sp[i]);break;
                            case "备注": deal.setRemarks( sp[i]);break;
                            case "柜员号": deal.setTellerNumber( sp[i]);break;
                            case "交易机构号": deal.setTradingOrganizationNumber( sp[i]);break;
                            case "对方机构号": deal.setInstitutionNumber( sp[i]);break;
                            default: break;
                        }
                    }
                }
                deal.setCasemanageId(uid);
                deal.setCreateId(id);
                deal.setIsDelete(0);
                deal.setCreateTime(new Timestamp(System.currentTimeMillis()));
                Date date1 = DateUtils.getDate(date);
                if (date1!=null)deal.setTradeDate(new Timestamp(date1.getTime()));
                if (deal.getAccountMoney().compareTo(new BigDecimal(0))<0){
                    deal.setSign(1);
                    //取绝对值

                } else {
                    deal.setSign(0);
                }
                if (deal.getAccountBodyCard()==null||"".equals(deal.getAccountBodyCard())){
                    deal.setAccountBodyCard(deal.getAccountBodyNumber());
                }
                if (deal.getAccountRivalCard()==null||"".equals(deal.getAccountRivalCard())){
                    deal.setAccountRivalCard(deal.getAccountRivalNumber());
                }
                deal.setUuid(uuid);

                list.add(deal);
            }
            List<DealFlow> temp = new ArrayList<DealFlow>();
            for(int i=0;i<list.size();i++) {
                temp.add(list.get(i));
                if(i==list.size()-1 || i%500==499) {
                	if(StringUtils.isEmpty(list.get(i).getAccountBodyCard()) && StringUtils.isEmpty(list.get(i).getAccountBodyNumber())) {
                		temp.remove(list.get(i));
                    }
                    dealFlowService.batchSave(temp);
                    temp = new ArrayList<DealFlow>();
                }
            }
            result.put("successNum", String.valueOf(list.size()));
            result.put("data", temp);
//            result.put("list", list);
            br.close();
            return result;
        } catch (Exception e) {
            System.out.println("文件读取错误!");
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 不固定模板，去标题池查找对应的标题，对应关系做好在做导入
     * 只能处理Excel类型的文件
     */
    public List<DealFlow> leadBillByTitlePoolExcel(Workbook wb){
        Sheet sheet = null;
        Row row = null;
        List<DealFlow> list = null;
        //数组用来记录map的key在表中行数据的下标
        String columns[] = new String[256];
        if(wb != null){
            //用来存放账单标题与标题池的对应关系
            Map<String , String> titleMap = new HashMap<>();
            //用来存放表单数据
            list = new ArrayList<DealFlow>();
            //获取第一个sheet
            int numberOfSheets = wb.getNumberOfSheets();
            //交易明细不一定是第一页
            for (int i = 0; i < numberOfSheets; i++) {
                if (wb.getSheetName(i).contains("交易明细")) {
                    sheet = wb.getSheetAt(i);
                    break;
                }
            }
            //找不到交易明细的情况下，默认第一页
            if (sheet==null)sheet = wb.getSheetAt(0);

            //获取最大行数
            int rownum = sheet.getLastRowNum();
            //记录行数，第一行不一定是标题行
            int i = 0;
            //从第一行开始解析，找到标题行
            for(i=i;i<rownum;i++) {
                row = sheet.getRow(i);
                if(!ExcelUtils.isBlankLine(row))continue;
                //获取最大列数
                int colnum = row.getPhysicalNumberOfCells();
                //判断这一行是否有合并单元格，或者列数小于5
                if (ExcelUtils.isMergedRegion(sheet, i, 0)||row.getPhysicalNumberOfCells()<5)continue;
                if ("".equals(ExcelUtils.getCellValue(row.getCell(3)).trim()))continue;
                //标题行与标题池做对应，找到标题对应字段
                for (int j=0;j<colnum;j++){
                    String cellData = ExcelUtils.getCellValue(row.getCell(j));
                    String replace = cellData.replace(" ", "");
//                    try {
//						replace = SecurityUtil.encrypt(replace);
//					} catch (UnsupportedEncodingException e) {
//						e.printStackTrace();
//					}
                    DealFlowTitlePool dealPool = dealFlowTitlePoolRepository.findByBankTitleName(replace);
                    if (null!=dealPool&&dealPool.getIsDelete()==0){
                        DealFlowTitle one = dealFlowTitleRepository.findOne(dealPool.getTitleId());
                        titleMap.put(cellData ,one.getMappingField());
                        columns[j] = cellData;
                    }
                }
                //标题对应完毕，跳出循环
                break;
            }

            //读取正文信息
            loop1:for ( i = i+1; i<=rownum; i++) {
                row = sheet.getRow(i);
                if(row !=null&&ExcelUtils.isBlankLine(row)){
                    int colnum = row.getPhysicalNumberOfCells();
                    DealFlow deal = new DealFlow();
                    //此行第一格内出现：就说明这一行不是数据行，跳过
                    if(ExcelUtils.getCellValue(row.getCell(0)).indexOf("：")>0)continue;
                    //这一行是否为合并行，出现合并行就跳过
                    if (ExcelUtils.isMergedRegion(sheet, i, 0)||row.getPhysicalNumberOfCells()<5)continue loop1;
                    //交易日期两个字段就拼接，一个字段则不拼接
                    String date = "";
                    loop2:for (int j=0;j<colnum;j++){

                        if (columns[j]!=null){
                            String cellData = ExcelUtils.getCellValue(row.getCell(j));
                            //对于出现了不固定的多行标题做判断，如果此行为标题行，直接跳过外层循环
                            if (columns[j].equals(cellData))continue loop1;
                            cellData = cellData.trim();
                            //把对应标题的数据放入实体中
                            switch (titleMap.get(columns[j])){
                                case "交易主体": deal.setAccountBody(cellData);break;
                                case "交易主体账号": deal.setAccountBodyNumber(cellData);break;
                                case "交易主体卡号": deal.setAccountBodyCard(cellData);break;
                                case "收付标志":
                                    if ("0".equals(cellData)||"C".equals(cellData)||"进".equals(cellData)||"贷".equals(cellData)){
                                        deal.setSign(0);
                                    } else if ("1".equals(cellData)||"D".equals(cellData)||"出".equals(cellData)||"借".equals(cellData)){
                                        deal.setSign(1);
                                    }break;
                                case "日期时间":
                                    if (cellData.length()<6)cellData = "000000";
                                    date += " "+cellData;
                                    date = date.trim() ;break ;
                                    //deal.setTradeDate(date.trim());break;
                                case "交易对手": deal.setAccountRival(cellData);break;
                                case "交易对手账号": deal.setAccountRivalNumber(cellData);break;
                                case "交易对手卡号": deal.setAccountRivalCard(cellData);break;
                                case "交易金额":
                                    if (!"".equals(cellData)&&cellData!=null&&new BigDecimal(cellData).compareTo(new BigDecimal(0))!=0){
                                        deal.setAccountMoney(new BigDecimal(cellData));
                                        if (columns[j].equals("贷方发生额")&&new BigDecimal(cellData).compareTo(new BigDecimal(0))>0){
                                            deal.setSign(0);
                                        } else if (columns[j].equals("借方发生额")&&new BigDecimal(cellData).compareTo(new BigDecimal(0))>0){
                                            deal.setSign(1);
                                        }
                                    }break;
                                case "交易后余额": if (!"".equals(cellData)&&cellData!=null)deal.setSurplusMoney(new BigDecimal( cellData));break;
                                case "摘要": deal.setContentAbstract( cellData);break;
                                case "交易主体归属行": deal.setAccountBodyBank( cellData);break;
                                case "交易对手归属行": deal.setAccountRivalBank( cellData);break;
                                case "交易地点": deal.setAccountAddress( cellData);break;
                                case "交易方式": deal.setAccountMode( cellData);break;
                                case "交易流水号": deal.setSerialNumber(cellData);break;
                                case "MAC地址": deal.setMacAddress( cellData);break;
                                case "IP地址": deal.setIpAddress( cellData);break;
                                case "币种": deal.setCurrency( cellData);break;
                                case "备注": deal.setRemarks( cellData);break;
                                case "柜员号": deal.setTellerNumber( cellData);break;
                                case "交易机构号": deal.setTradingOrganizationNumber( cellData);break;
                                case "对方机构号": deal.setInstitutionNumber( cellData);break;
                                default: break;
                            }
                        }
                    }
                    Date date1 = DateUtils.getDate(date);
                    if (date1!=null)deal.setTradeDate(new Timestamp(date1.getTime()));
                    if (deal.getAccountBodyCard()==null||"".equals(deal.getAccountBodyCard())){
                        deal.setAccountBodyCard(deal.getAccountBodyNumber());
                    }
                    if (deal.getAccountRivalCard()==null||"".equals(deal.getAccountRivalCard())){
                        deal.setAccountRivalCard(deal.getAccountRivalNumber());
                    }

                    list.add(deal);
                }else{
                    continue;
                }

            }
            return list;
        }
        return list;
    }

    /**
     * 加密
     */
    public void encryption() {
        try {
            Iterable<DealFlowTitlePool> tps = dealFlowTitlePoolRepository.findAll();
            if (null != tps) {
                for (DealFlowTitlePool tp : tps) {
                    String titleName = tp.getBankTitleName();
//                    String encrypt = SecurityUtil.encrypt(titleName);
//                    tp.setBankTitleName(encrypt);
//                    System.out.println("titleName=" + encrypt);
                    tp.setCreateId(1);
                    tp.setCreateTime(new Date());
                }
            }
            dealFlowTitlePoolRepository.save(tps);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    /**
     * 解密
     */
    public void decrypt() {
        try {
            Iterable<DealFlowTitlePool> tps = dealFlowTitlePoolRepository.findAll();
            if (null != tps) {
                for (DealFlowTitlePool tp : tps) {
                    String decrypt = SecurityUtil.decrypt(tp.getBankTitleName());
                    tp.setBankTitleName(decrypt);
                }
            }
            dealFlowTitlePoolRepository.save(tps);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}
